package com.sgcc.manager.sql.controller;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;


import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import org.w3c.dom.*;

import java.net.URL;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.sgcc.manager.sql.biz.ISqlBiz;
import com.sgcc.manager.sql.excelUtil.JXLExcelView;
import com.sgcc.manager.sql.excelUtil.User;
import com.sgcc.manager.sql.excelUtil.Xls2Excel;
import com.sgcc.manager.sql.helper.ISqlComponent;
import com.sgcc.manager.sql.util.MimeUtil;
import com.sgcc.manager.sql.util.SqlClassConstant;
import com.sgcc.manager.sql.util.SqlClassUtil;
import com.sgcc.manager.sql.util.StackTraceUtil;
import com.sgcc.manager.sql.util.WebUtils;
import com.sgcc.manager.sql.vo.HistoryVO;

/**
 * <br>Title:TODO 类标题
 * <br>Description:TODO 类功能描述
 * <br>Author:王腾龙
 * <br>Date:Mar 4, 2015
 * spring MVC 3.1 注解:@ResponseBody 返回json数据：
 * http://www.07net01.com/linux/spring_MVC_3_1_zhujie__ResponseBody_fanhuijsonshuju_544130_1374308313.html
 * 
 * ajax详解：http://blog.sina.com.cn/s/blog_4f925fc30100la36.html
 * 
 * Spring 注解学习手札（七） 补遗——@ResponseBody，@RequestBody，@PathVariable：
 * http://snowolf.iteye.com/blog/1628861
 * 
 */
@Controller
public class SqlController {
	private static final long serialVersionUID = -4749964156847039919L;
	private String fileName="historyDB.xml";
	public static final String PLANT="PLANT";
	public static final String KEY="key";
	public static final String VALUE="VALUE";
	Logger logger = LoggerFactory.getLogger(SqlController.class.getName());
	// AJAX调用后返回操作状态编码和原因的对象
	protected JSONObject jsonObjResult = new JSONObject();
	@Autowired
	private ISqlBiz sqlBizImpl;
	@Autowired
	private ISqlComponent sqlComponentImpl;
	/***
	 * 进入sql显示页
	 * @date 2015-2-9 下午03:12:47
	 * @return
	 *
	 *http://www.cnblogs.com/QQParadise/articles/1651825.html
	 *http://blog.csdn.net/zhangshufa/article/details/6337743
	 */
	@RequestMapping("/")
	public String index(Model model,HttpServletRequest request){
		HttpSession session=request.getSession();
		List historys=findHistory();
		model.addAttribute("history", historys);
		String sessionId = session.getId();
		System.out.println("---->SessionID:"+sessionId);
		SqlController.class.getClassLoader().getResource("");// /D:/Oracle/Middleware/user_projects/domains/erms/autodeploy/sqlWeb/WEB-INF/classes/
		URL url=SqlController.class.getResource("");
//		System.out.println(url);// /D:/Oracle/Middleware/user_projects/domains/erms/autodeploy/sqlWeb/WEB-INF/classes/com/sgcc/manager/
		Map dataMap=(Map) session.getAttribute(sessionId);
		if(dataMap !=null){
			System.out.println("-------已登录----------");
			model.addAttribute("type", dataMap.get("type"));
			System.out.println("状态："+dataMap.get("type"));
			model.addAttribute("ipAdd", dataMap.get("ipAdd"));
			model.addAttribute("db", dataMap.get("db"));
			model.addAttribute("username", dataMap.get("username"));
			System.out.println("name:"+dataMap.get("username"));
			model.addAttribute("password", dataMap.get("password"));
			model.addAttribute("oracleUrl", dataMap.get("oracleUrl"));
			model.addAttribute("msg","数据库已经连接");
		}
		return "index";
	}
	
	/**
	 * 
	 * <br>Description:TODO 加载数据库历史连接记录
	 * @param model
	 * @return
	 */
	@RequestMapping(value = "loadXml")
	@ResponseBody
	public Object loadXml(Model model){
		boolean bool=true;
		try {
			List historys=findHistory();
			model.addAttribute("history", historys);
			model.addAttribute("deleteBool", bool);
		} catch (Exception e) {
			bool=false;
			logger.info("---------xml记录删除出错误--------");
			logger.error(e.toString());
			e.printStackTrace();
		}
		return JSON.toJSON(model);
	}
	
	/**
	 * 
	 * <br>Description:删除xml中的历史连接记录
	 * @param request
	 * @param session
	 * @param model
	 * @return
	 */
	@RequestMapping(value = "deleteXml")
	@ResponseBody
	public Object deleteXml(HttpServletRequest request,HttpSession session,Model model){
		boolean bool=false;
		String xmlKey= WebUtils.getStrParamOrEmpty(request, "key");//sql语句
		String xmlValue=WebUtils.getStrParamOrEmpty(request, "value");
		System.out.println("删除key---"+xmlKey);
		DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
		try {
			DocumentBuilder dbBuilder = dbf.newDocumentBuilder();
			InputStream inputStream=SqlController.class.getClassLoader().getResourceAsStream(fileName);
			Document doc=dbBuilder.parse(inputStream);
			NodeList nodeList=doc.getFirstChild().getChildNodes();
			for (int i = 0; i < nodeList.getLength(); i++) {
				Node node=nodeList.item(i);
				String value=node.getTextContent();
				if (value.equals(xmlValue)) {
					//删除node节点方法---->先找到父节点，在由父节点删除此node节点
					node.getParentNode().removeChild(node);  
					inputStream.close();
					//将更新后的记录写入记录到xml文件
					TransformerFactory tff = TransformerFactory.newInstance();
		            Transformer tf = tff.newTransformer();
		            DOMSource source = new DOMSource(doc);
		            URL url=SqlController.class.getClassLoader().getResource(fileName);
		            StreamResult rs = new StreamResult(new File(url.getPath()));
		            tf.transform(source, rs);
					bool=true;
					break;
				}
			}
			if(bool){
				List historys=findHistory();
				model.addAttribute("history", historys);
			}
		} catch (Exception e) {
			logger.info("---------xml记录删除出错误--------");
			logger.error(e.toString());
			e.printStackTrace();
		}
		model.addAttribute("deleteBool", bool);
		return JSON.toJSON(model);
	}
	/**
	 * Description:添加连接记录到xml文件
	 * @param ipAdd ip地址
	 * @param db 数据库
	 * @param username 用户名
	 * @param password 密码
	 * @param type 数据库类型：oracle或者mysql
	 */
	private void addHistory2XML(String ipAdd, String db, String username,String password, String type,String oracleUrl) {
		String key;
		String value;
		if("oraDBs".equals(type)){
			key=oracleUrl.substring(oracleUrl.indexOf("service_name")+13, oracleUrl.indexOf(")))"));
			System.out.println(key);
			key=key+"&"+username+"&"+password;
			value=type+"&"+oracleUrl+"&"+username+"&"+password;
		}else{
			key=username+"&"+ipAdd+"&"+db;
			value=type+"&"+key+"&"+password;
		}
		System.out.println("key:"+key+"  value:"+value);
		HistoryVO history=new HistoryVO(key,value);
		List list=findHistory();
		boolean bool=true;
		//判断xml文件中是否已包含相同的记录
		for (int i = 0; i < list.size(); i++) {
			HistoryVO oldHistory=(HistoryVO) list.get(i);
			if (oldHistory.getValue().equals(value)) {
				 bool=false;
			}
		}
		if(bool){
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
				try {
					DocumentBuilder dbbuilder = dbf.newDocumentBuilder();
					InputStream inputStream=SqlController.class.getClassLoader().getResourceAsStream(fileName);
					Document doc = dbbuilder.parse(inputStream);
					add(doc,history);
					
					//写入记录到xml文件
					TransformerFactory tff = TransformerFactory.newInstance();
		            Transformer tf = tff.newTransformer();
		            DOMSource source = new DOMSource(doc);
		            URL url=SqlController.class.getClassLoader().getResource(fileName);
		            StreamResult rs = new StreamResult(new File(url.getPath()));
		            tf.transform(source, rs);
				}  catch (Exception e) {
					logger.info("---------数据库连接记录写入到xml错误--------");
					logger.error(e.toString());
					e.printStackTrace();
				}
			
		}
		
		
	}
	
	/** Description:追加记录到Dom节点集合
	 * @param doc
	 * @param history 记录对象
	 * 节点格式：<PLANT Key='key数据'>value数据</PLANT>
	 */
    public void add(Document doc, HistoryVO history) {       
        Element newChild=doc.createElement(PLANT);
        newChild.setAttribute(KEY, history.getKey());
//        Text keyText=doc.createTextNode(history.getKey());
//        Text valText=doc.createTextNode(history.getValue());
//        newChild.appendChild(keyElement).appendChild(keyText);
        Text valText=doc.createTextNode(history.getValue());
        newChild.appendChild(valText);
        doc.getFirstChild().appendChild(newChild);
    }
	
    /**
     * 
     * <br>Description:查询xml中保存的历史记录
     * @return
     */
	public List findHistory() {
		List<HistoryVO> list=null;
		DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
		try {
			DocumentBuilder db = dbf.newDocumentBuilder();
			InputStream inputStream=SqlController.class.getClassLoader().getResourceAsStream(fileName);
			Document doc=db.parse(inputStream);
			NodeList plantList=doc.getFirstChild().getChildNodes();
			list=new ArrayList<HistoryVO>();
			for (int i = 0; i < plantList.getLength(); i++) {
//				NodeList childList=plantList.item(i).getChildNodes();
//				for (int j = 0; j < childList.getLength(); j++) {
//					Node node=childList.item(j);
//					if(KEY.equals(node.getNodeName())){  
//                        System.out.println(node.getNodeName()+"---"+node.getTextContent());  
//                    }  
//                    if(VALUE.equals(node.getNodeName())){  
//                        System.out.println(node.getNodeName()+"---"+node.getTextContent());  
//                    }  
//				}
				Node node=plantList.item(i);
				//得到节点的属性集合
				NamedNodeMap  attrsMap=node.getAttributes();
				Node keyNode=attrsMap.getNamedItem("key");
				String key=keyNode.getTextContent();
				String value=node.getTextContent();
				HistoryVO history=new HistoryVO(key,value);
				list.add(history);
			}
			return list;
		} catch (Exception e) {
			logger.info("---------数据库连接记录从xml中查询错误--------");
			logger.error(e.toString());
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * Description:根据ip,用户名,密码连接数据库
	 * @param ipAdd IP地址
	 * @param db 数据库SID号
	 * @param username 用户名
	 * @param password 密码
	 * @param type 数据库类型(oracle或者oracle集群)
	 * @param oracleUrl oracle集群的url地址 非集群连接的话，可以为null
	 * @param model
	 * @param session
	 * @return
	 */
	@RequestMapping(value="login",method=RequestMethod.POST)
	@ResponseBody
	public Object login(String ipAdd,String db, String username,String password,String type,String oracleUrl,Model model,HttpSession session){
		String sessionId = session.getId();
		session.removeAttribute(sessionId);
		System.out.println("---->SessionID:"+sessionId);
		model.addAttribute("ipAdd", ipAdd);
		model.addAttribute("db", db);
		model.addAttribute("username", username);
		model.addAttribute("password", password);
		model.addAttribute("type", type);
		model.addAttribute("oracleUrl", oracleUrl);
		System.out.println("登陆信息--ipAdd:"+ipAdd+"   db:"+db+"   username:"+username+"   pwd:"+password+"  oracleURL:"+oracleUrl);
		boolean bool;
		if(type.equals("oraDBs")){
			bool=sqlBizImpl.LinkDB(sessionId,oracleUrl, username, password, type);
		}else {
			bool=sqlBizImpl.LinkDB(sessionId,ipAdd, db, username, password,type);
		}
		
		if(bool){
			model.addAttribute("msg","连接成功");
			addHistory2XML(ipAdd,db,username,password,type,oracleUrl);
			Map loginMap=new HashMap<String,String>();
			loginMap.put("type", type);
			loginMap.put("ipAdd", ipAdd);
			loginMap.put("db", db);
			loginMap.put("username", username);
			loginMap.put("password", password);
			loginMap.put("oracleUrl", oracleUrl);
			session.setAttribute(sessionId,loginMap);
		}else{
			model.addAttribute("msg","连接失败");
		}
		return JSON.toJSON(model);
		
	}
	
	/**
	 * 
	 * <br>Description:执行sql查询操作
	 * @param request
	 * @param session
	 * @return
	 */
	@RequestMapping(value = "executeSql")
	@ResponseBody
	public Object executeSql(HttpServletRequest request,HttpSession session){
		String sessionId = session.getId();
		//定义变量
		boolean flag=true;
		String msg="执行成功!";
		//获取sql信息
		String sql = WebUtils.getStrParamOrEmpty(request, "sql");//sql语句
		String currentPage= WebUtils.getStrParamOrEmpty(request, "currentPage");   //当前页号
		//处理换行符
		sql=SqlClassUtil.DealSql(sql);
		//获取操作
		String operation=SqlClassUtil.autoClass(sql);
		jsonObjResult.put("operation", operation);
		try {
			//根据不同的操作获取不同操作
			if(operation.equalsIgnoreCase(SqlClassConstant.OPTION_SELECT)){
				if(StringUtils.isEmpty(currentPage)){
					currentPage="1";
				}
				Map resultMap=this.sqlBizImpl.queryForMap(sessionId,sql, Integer.parseInt(currentPage));
				jsonObjResult.put("resultMap", resultMap);
			}else if (operation.equalsIgnoreCase(SqlClassConstant.OPTION_UPDATE)){
				int  row=this.sqlBizImpl.update(sessionId,sql);
				//int  row=this.sqlComponentImpl.update(sql, this.request);
				jsonObjResult.put("row", row);
			}else if (operation.equalsIgnoreCase(SqlClassConstant.OPTION_DLL)){
				this.sqlBizImpl.execute(sessionId,sql);
			}else {
				flag=false;
				msg="没有匹配上值";
			}
		} catch (Exception e) {
			flag=false;
			msg="sql异常！";
			List<String> errorList=StackTraceUtil.getStackTraceList(e);
			StringBuffer errorDetail=new StringBuffer();
			if(errorList!=null && errorList.size()>0){
				for (String error : errorList) {
					errorDetail.append(error).append("<br/>");
				}
			}
			jsonObjResult.put("errorDetail", errorDetail);
			e.printStackTrace();
		}
		jsonObjResult = this.initJsonObjResult(flag, msg);
		return JSON.parseObject(jsonObjResult.toJSONString());
		//字符串转json对象(用法同上)
//		this.doAjaxObjectWrite(jsonObjResult.toJSONString(), null, null, null,request,response);
		
/*		//json对象输出到前端页面的另一种实现方法
		response.setContentType("text/html");
		PrintWriter out;
		try {
			out = response.getWriter();
			out.print(jsonObjResult.toJSONString());
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
*/
	}
	
	@RequestMapping(value="/JSON")
	@ResponseBody
	public Object testJSON(ModelMap modelMap,HttpServletRequest request){
		String text = "{\"age\":105,\"id\":\"testFastJson001\",\"name\":\"maks\"}";  
        JSONObject json = JSON.parseObject(text);  
        System.out.println("parseJsonObject()方法：json==" + json);  
		return json;
	}
	
	@RequestMapping(value="nextPage")
	@ResponseBody
	public Object nextPage(HttpServletRequest request, HttpServletResponse response,HttpSession session){
		String sessionId=session.getId();
		//定义变量
		boolean flag=true;
		String msg="执行成功!";
		//获取sql信息
		String sql = WebUtils.getStrParamOrEmpty(request, "pageSql");//档案级别
		String type= WebUtils.getStrParamOrEmpty(request, "type");//
		String currentPage= WebUtils.getStrParamOrEmpty(request, "currentPage");
		String operation=WebUtils.getStrParamOrEmpty(request, "operation");
		String totalPages=WebUtils.getStrParamOrEmpty(request, "totalPages");
		//处理换行符
		sql=SqlClassUtil.DealSql(sql);
		//获取操作
		jsonObjResult.put("operation", operation);
		try {
			//根据不同的操作获取不同操作
			if(operation.equalsIgnoreCase(SqlClassConstant.OPTION_SELECT)){
				if(StringUtils.isEmpty(currentPage)){
					currentPage="1";
				}
				Map resultMap=this.sqlBizImpl.queryPageForMap(sessionId,sql, type, Integer.parseInt(currentPage),  Integer.parseInt(totalPages));
				//String jsonString=JsonUtils.toJSONString(resultMap, null);
				jsonObjResult.put("resultMap", resultMap);
				//获取列名
			}
		} catch (Exception e) {
			flag=false;
			msg="sql异常！";
			List<String> errorList=StackTraceUtil.getStackTraceList(e);
			StringBuffer errorDetail=new StringBuffer();
			if(errorList!=null && errorList.size()>0){
				for (String error : errorList) {
					errorDetail.append(error).append("<br/>");
				}
			}
			jsonObjResult.put("errorDetail", errorDetail);
			e.printStackTrace();
		}
		jsonObjResult = this.initJsonObjResult(flag, msg);
		//logger.info("执行sql语句输出信息:"+jsonObjResult.toJSONString());
		return JSON.parseObject(jsonObjResult.toJSONString());
//		this.doAjaxObjectWrite(jsonObjResult.toJSONString(), null, null, null,request,response);
	}
	
	public JSONObject initJsonObjResult(Boolean result, Object resultInfo) {
		jsonObjResult.put("result", result);
		jsonObjResult.put("resultInfo", resultInfo);
		// jsonObjResult.put("resultCode", resultCode);
		return jsonObjResult;
	}
	/**
	 * Ajax请求时向页面写入数据
	 * 
	 * @author LCC
	 * @param 输出内容
	 *            ,mime类型,mime字符集,页面字符集
	 * @param ajax
	 *            string,mime type,mime character encoding,character encoding
	 * @throws IOException
	 */
	public void doAjaxObjectWrite(String ajaxStr, String mime, String mimeCE, String ce,HttpServletRequest request, HttpServletResponse response) {
		if (ajaxStr != null && ajaxStr.length() > 0) {
			mime = mime == null ? MimeUtil.DEFAULT : mime;
			mimeCE = mimeCE == null ? MimeUtil.DEFAULT_MIME_CE : mimeCE;
			ce = ce == null ? MimeUtil.DEFAULT_CHARACTER_ENCODING : ce;

			String ct = mime + ";charset=" + mimeCE;

			response.setContentType(ct);
			response.setHeader("Cache-Control", "no-cache");
			response.setCharacterEncoding(ce);
			try {
				response.getWriter().write(ajaxStr);
				response.getWriter().flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 
	 * <br>Description:导出数据到前端excel文件
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping(value="exportExcel")
	@SuppressWarnings("unchecked")
	public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
		HttpSession session=request.getSession();
		String sessionId=session.getId();
		//获取参数
		String sql = WebUtils.getStrParamOrEmpty(request, "pageSql");
		String currentPage= WebUtils.getStrParamOrEmpty(request, "currentPage");
		String type=WebUtils.getStrParamOrEmpty(request,"excelType");
		String totalPages=WebUtils.getStrParamOrEmpty(request, "totalPages");
		//处理换行符
		sql=SqlClassUtil.DealSql(sql);
		ServletOutputStream fos = null;
		String dateStr = new String();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date time = new Date();
		dateStr = sdf.format(time);
		//导出excel文件名命名
		String exportName="查询导出("+dateStr+")";
		String excelName = exportName + ".xls";
		try {
			//查询map
//			Map dataMap=this.sqlBizImpl.queryForMap(sessionId,sql, Integer.parseInt(currentPage));
			Map dataMap=this.sqlBizImpl.queryPageForMap(sessionId,sql, type, Integer.parseInt(currentPage),  Integer.parseInt(totalPages));
			response.setContentType("application/x-msdownload ");
			excelName= new String(excelName.getBytes("gbk"), "ISO8859-1");// 解决中文 文件名问题
			response.setHeader("content-disposition", "attachment; filename="+ excelName);// 设定输出文件头
			fos = response.getOutputStream();
			//通过jxl包导出excel
			//this.sqlBizImpl.exportExcel(dataMap, fos,exportName);
			
			//通过HSSF控件导出execl
            Xls2Excel.Map2Excel(fos,dataMap,exportName,"fieldList","valueList");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fos.flush();
				fos.close();
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	@RequestMapping(value="exportExcel2")
	public void exportExcel2(HttpServletRequest request,HttpServletResponse response) throws Exception{
		HttpSession session=request.getSession();
		String sessionId=session.getId();
		//获取参数
		String sql = WebUtils.getStrParamOrEmpty(request, "pageSql");//档案级别
		//String type= WebUtils.getStrParamOrEmpty(request, "type");//
		String currentPage= WebUtils.getStrParamOrEmpty(request, "currentPage");
		String operation=WebUtils.getStrParamOrEmpty(request, "operation");
		String totalPages=WebUtils.getStrParamOrEmpty(request, "totalPages");
		
		ServletOutputStream fos = null;
		String dateStr = new String();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date time = new Date();
		dateStr = sdf.format(time);
		//导出excel文件名命名
		String exportName="查询导出("+dateStr+")";
		String excelName = exportName + ".xls";
		try {
			fos = response.getOutputStream();
			//查询map
			Map dataMap=this.sqlBizImpl.queryForMap(sessionId,sql, Integer.parseInt(currentPage));
			//导出excel
//			this.sqlBizImpl.exportExcel(dataMap, fos,exportName);
			File file = new File(excelName);
			response.setContentType("application/vnd.ms-excel");
			response.addHeader("Content-Disposition", "inline;filename="+ new String(excelName.getBytes("GBK"), "ISO8859-1"));
			response.setHeader("Content-Length", String.valueOf(file.length()));
			
			java.io.BufferedInputStream bis = null;
			java.io.BufferedOutputStream bos = null;
				bis = new BufferedInputStream(new FileInputStream(file));
				bos = new BufferedOutputStream(response.getOutputStream());
				int space = bis.available();
				byte data[] = new byte[space];
				int bytesRead;
				while (-1 != (bytesRead = bis.read(data, 0, data.length))) {
					bos.write(data, 0, bytesRead);
				}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fos.flush();
				fos.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	
	
	/**
	 * 
	 * <br>Description:POI方式导出excel的测试Demo
	 * @param request
	 * @param response
	 * @param model
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
    @RequestMapping(value = "export")
    public void exportOne(HttpServletRequest request,HttpServletResponse response, Model model) throws Exception {
        Map map = new HashMap<String, String>();
        map.put("活动", "活动A");
        map.put("项目", "项目A");
        exportExcel(request, response, map);
    }
 
    @SuppressWarnings("unchecked")
    private void exportExcel(HttpServletRequest request,HttpServletResponse response, Map map) {
        // 设置导出excel的相关样式
        String fileName = "活动报名汇总.xls";
        try {
        	fileName = new String(fileName.getBytes("gbk"), "ISO8859-1");// 解决中文 文件名问题
        	response.setContentType("application/x-msdownload");// 定义输出类型
        	response.setHeader("content-disposition", "attachment; filename="+ fileName);// 设定输出文件头
            OutputStream os = response.getOutputStream();
            Xls2Excel.xlsDto2Excel(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }   
    
    /**
     * 
     * <br>Description:TODO 前端直接打开excel文件--jxl方式导出
     * @param model
     * @param reqeust
     * @param response
     * @param startTime
     * @param endTime
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
	@RequestMapping(value = "/daily", method = RequestMethod.GET)  //打开
	public @ResponseBody
	void DailyWorkExportOperator(Model model,
			HttpServletRequest reqeust,
			HttpServletResponse response,
			@RequestParam(required = false) String startTime,
			@RequestParam(required = false) String endTime) throws IOException, RowsExceededException, WriteException {
				String as = "zhoubao.xls";
				File file = new File(as);
				if(!file.exists()){
					file.createNewFile();
				}
				WritableWorkbook wwb=null;
				wwb = Workbook.createWorkbook(file); 
				if(wwb!=null){
					WritableSheet ws=wwb.createSheet("sheet1", 0);
					for(int a=0;a<2;a++){
						for(int b=0;b<5;b++){
							Label label=new Label(b,a,"内容"+b);
							ws.addCell(label);
						}
					}
					wwb.write();
					wwb.close();
				}
				
				response.reset();
				
		        String fileName = as;// = java.net.URLEncoder.encode(as, "UTF-8");
		        fileName = new String(as.getBytes("GB2312"), "ISO_8859_1"); 
		        as = fileName;
				response.setContentType("application/vnd.ms-excel");
				response.addHeader("Content-Disposition", "inline;filename="+as );
				response.setHeader("Content-Length", String.valueOf(file.length()));
				
				java.io.BufferedInputStream bis = null;
				java.io.BufferedOutputStream bos = null;
					bis = new BufferedInputStream(new FileInputStream(file));
					bos = new BufferedOutputStream(response.getOutputStream());
					int space = bis.available();
					byte data[] = new byte[space];
					int bytesRead;
					while (-1 != (bytesRead = bis.read(data, 0, data.length))) {
						bos.write(data, 0, bytesRead);
					}
					bos.flush();
					if (bis != null)
						bis.close();
					if (bos != null)
						bos.close();
	}  
	
	/**
	 * 
	 * <br>Description:TODO jxl导出excel文件Demo
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping("/jxlExcel")  
    public ModelAndView viewJxlExcel(HttpServletRequest request,  HttpServletResponse response) {       
        String [] array1 = null;  
        String colums="name";
        if(null != colums && colums.indexOf(",") != -1){  
            array1 = colums.split(",");  
        } 
        String titles="姓名";
        String [] array2 = null;  
        if(null != titles && titles.indexOf(",") != -1){  
            array2 = titles.split(",");  
        }             
        Map model = new HashMap();  
        // 构造数据  
        List<User> users = new ArrayList<User>();  
        users.add(new User("123456", "李逵", "123", "成都市", "1", 23));  
        users.add(new User("123457", "李四", "124", "北京市", "2", 53));  
        users.add(new User("123458", "李三", "125", "河南市", "0", 73));  
        users.add(new User("123459", "李五", "126", "大路市", "3", 93));  
        model.put("list", users);         
        model.put("columns", array1);  
        model.put("titles", array2);          
        return new ModelAndView(new JXLExcelView(), model);  
    }  
}
